# coding=utf-8
import pymysql

file_path = "phoneno.csv"
table_name = "sys_area_phone"

con = pymysql.connect(user="root",
                      passwd="qyX=J63ia",
                      db="mcs_db",
                      host="localhost",
                      local_infile=1)
try:
    con.set_charset('utf8mb4')
    cur = con.cursor()
    try:
        cur.execute("set names utf8mb4")
        cur.execute("SET character_set_connection=utf8mb4;")

        data = {}

        f = open(file_path, 'r', encoding='utf8')
        all_lines = f.readlines()
        for line in all_lines:
            # 做成列表
            line_data = line.split(',')
            # 去除最后的换行符
            line_data[-1] = line_data[-1].rstrip('\n')
            print(line_data)
            data[line_data[0]] = line_data

        # for key in data.keys():
            sql = "INSERT INTO sys_area_temp(phone_no,province,city,area_name,isp_name,isp_desc)" \
                  " VALUES(%s,%s,%s,%s,%s,%s)"
            cur.execute(sql, line_data)
            con.commit()
    finally:
        cur.close()
except IOError:
    con.rollback()
finally:
    con.close()
